﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GHRMS.IDAL;
using KPIS.GERP.GHRMS.MODEL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class MasNamePrefixDAL : IMasNamePrefixDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public MasNamePrefixDAL()
        { 
        
        }
        public DataSet LoadNamePrefixes(MasNamePrefixInfo pfInfo)
        {
            string strSQL;
            int gender = pfInfo.Genders == null ? 0 : pfInfo.Genders.Sequence;
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", pfInfo.Sequence);
                dbManager.AddParameters(2, "@Genders", gender);

                strSQL = "SELECT np.NAME_PREFIX_SEQ, np.PREFIX_NAME, np.PREFIX_SHORT, np.PREFIX_NAME_ENG"
                            + ", np.PREFIX_SHORT_ENG, np.GENDER_SEQ, g.GENDER_NAME, np.CREATED_BY, np.UPDATED_BY"
                     + " FROM MAS_NAME_PREFIXES np"
                     + " LEFT JOIN MAS_GENDERS g ON np.GENDER_SEQ = g.GENDER_SEQ"
                     + " WHERE np.RECORD_STATUS not like @DeleteRecord"
                     + (pfInfo.Sequence > 0 ? " AND np.NAME_PREFIX_SEQ = @Sequence " : "")
                     + (gender > 0 ? " AND (np.GENDER_SEQ = @Genders OR np.GENDER_SEQ IS NULL )" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                
                return ds;

            }
            catch (Exception ex)
            {
                return null;
                throw new Exception("{class MasNamePrefixDAL}:" + ex.Message);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public DataSet LoadNamePrefixesENG(MasNamePrefixInfo pfInfo)
        {
            string strSQL;
            int gender = pfInfo.Genders == null ? 0 : pfInfo.Genders.Sequence;
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", pfInfo.Sequence);
                dbManager.AddParameters(2, "@Genders", gender);

                strSQL = "SELECT np.NAME_PREFIX_SEQ, np.PREFIX_NAME, np.PREFIX_SHORT, np.PREFIX_NAME_ENG"
                            + ", np.PREFIX_SHORT_ENG, np.GENDER_SEQ, g.GENDER_NAME, np.CREATED_BY, np.UPDATED_BY"
                     + " FROM MAS_NAME_PREFIXES np"
                     + " LEFT JOIN MAS_GENDERS g ON np.GENDER_SEQ = g.GENDER_SEQ"
                     + " WHERE np.RECORD_STATUS not like @DeleteRecord"
                     + " AND coalesce(np.PREFIX_NAME_ENG, '') not like ''"
                     + (pfInfo.Sequence > 0 ? " AND np.NAME_PREFIX_SEQ = @Sequence " : "")
                     + (gender > 0 ? " AND (np.GENDER_SEQ = @Genders OR np.GENDER_SEQ IS NULL )" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;

            }
            catch (Exception ex)
            {
                return null;
                throw new Exception("{class MasNamePrefixDAL}:" + ex.Message);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public IList<MasNamePrefixInfo> ListNamePrefixes(MasNamePrefixInfo pfInfo)
        {
            string strSQL;
            int gender = pfInfo.Genders == null ? 0 : pfInfo.Genders.Sequence;
            List<MasNamePrefixInfo> infoList = new List<MasNamePrefixInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", pfInfo.Sequence);
                dbManager.AddParameters(2, "@Genders", gender);

                strSQL = "SELECT NAME_PREFIX_SEQ, PREFIX_NAME, PREFIX_SHORT, PREFIX_NAME_ENG, PREFIX_SHORT_ENG"
                            + ", GENDER_SEQ, CREATED_BY, UPDATED_BY"
                     + " FROM MAS_NAME_PREFIXES"
                     + " WHERE RECORD_STATUS not like @DeleteRecord"
                     + (pfInfo.Sequence > 0 ? " AND NAME_PREFIX_SEQ = @Sequence " : "")
                     + (gender > 0 ? " AND (GENDER_SEQ = @Genders OR GENDER_SEQ IS NULL )" : "");

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    //MasGenderDAL genderDAL = new MasGenderDAL();
                    MasGenderInfo genderInfo = new MasGenderInfo();
                    genderInfo.Sequence = (rdr.IsDBNull(5)) ? 0 : rdr.GetInt32(5);
                    //IList<MasGenderInfo> genderList = genderDAL.ListMasGenders(new MasGenderInfo((rdr.IsDBNull(5)) ? 0 : rdr.GetInt32(5)));
                    //if (genderList.Count > 0)
                    //    genderInfo = genderList[0];

                    MasNamePrefixInfo Info = new MasNamePrefixInfo(rdr.GetInt32(0)
                        , (rdr.IsDBNull(1)) ? "" : rdr.GetString(1)
                        , (rdr.IsDBNull(2)) ? "" : rdr.GetString(2)
                        , (rdr.IsDBNull(3)) ? "" : rdr.GetString(3)
                        , (rdr.IsDBNull(4)) ? "" : rdr.GetString(4)
                        , genderInfo
                        , (rdr.IsDBNull(6)) ? (int?)null : rdr.GetInt32(6)
                        , (rdr.IsDBNull(7)) ? (int?)null : rdr.GetInt32(7));
                    infoList.Add(Info);
                }
                return infoList;
                
            }
            catch (Exception  ex )
            {               
                return null;
                throw new Exception("{class MasNamePrefixDAL}:" + ex.Message); 
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public IList<MasNamePrefixInfo> ListNamePrefixes(string searchText)
        {
            string strSQL; 
            List<MasNamePrefixInfo> infoList = new List<MasNamePrefixInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@SearchText", searchText); 

                strSQL = "SELECT " +
                         "NAME_PREFIX_SEQ" +
                         ", PREFIX_NAME" +
                         ", PREFIX_SHORT" +
                         ", PREFIX_NAME_ENG" +
                         ", PREFIX_SHORT_ENG"
                            + ", GENDER_SEQ, CREATED_BY, UPDATED_BY"
                     + " FROM MAS_NAME_PREFIXES"
                     + " WHERE RECORD_STATUS not like @DeleteRecord" +
                " and ( PREFIX_NAME like CONCAT('%', @SearchText , '%')  or " +
                " PREFIX_SHORT like CONCAT('%', @SearchText , '%') or " +
                " PREFIX_NAME_ENG like CONCAT('%', @SearchText , '%') or " +
                " PREFIX_SHORT_ENG like CONCAT('%', @SearchText , '%') )";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    //MasGenderDAL genderDAL = new MasGenderDAL();
                    MasGenderInfo genderInfo = new MasGenderInfo();
                    genderInfo.Sequence = (rdr.IsDBNull(5)) ? 0 : rdr.GetInt32(5);
                    //IList<MasGenderInfo> genderList = genderDAL.ListMasGenders(new MasGenderInfo((rdr.IsDBNull(5)) ? 0 : rdr.GetInt32(5)));
                    //if (genderList.Count > 0)
                    //    genderInfo = genderList[0];

                    MasNamePrefixInfo Info = new MasNamePrefixInfo(rdr.GetInt32(0)
                        , (rdr.IsDBNull(1)) ? "" : rdr.GetString(1)
                        , (rdr.IsDBNull(2)) ? "" : rdr.GetString(2)
                        , (rdr.IsDBNull(3)) ? "" : rdr.GetString(3)
                        , (rdr.IsDBNull(4)) ? "" : rdr.GetString(4)
                        , genderInfo
                        , (rdr.IsDBNull(6)) ? (int?)null : rdr.GetInt32(6)
                        , (rdr.IsDBNull(7)) ? (int?)null : rdr.GetInt32(7));
                    infoList.Add(Info);
                }
                return infoList;

            }
            catch (Exception ex)
            {
                return null;
                throw new Exception("{class MasNamePrefixDAL}:" + ex.Message);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        public void Insert(MasNamePrefixInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@Name", Info.Name);
                dbManager.AddParameters(1, "@NameShort", Info.NameShort);
                dbManager.AddParameters(2, "@NameEng", Info.NameEng);
                dbManager.AddParameters(3, "@NameEngShort", Info.NameEngShort);
                dbManager.AddParameters(4, "@GenderSeq", Info.Genders.Sequence);
                dbManager.AddParameters(5, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(7, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO MAS_NAME_PREFIXES(PREFIX_NAME, PREFIX_SHORT, PREFIX_NAME_ENG"
                        + ", PREFIX_SHORT_ENG, GENDER_SEQ, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@Name, @NameShort, @NameEng, @NameEngShort, @GenderSeq"
                        + ", @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(MasNamePrefixInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@Sequence", Info.Sequence);
                dbManager.AddParameters(1, "@Name", Info.Name);
                dbManager.AddParameters(2, "@NameShort", Info.NameShort);
                dbManager.AddParameters(3, "@NameEng", Info.NameEng);
                dbManager.AddParameters(4, "@NameEngShort", Info.NameEngShort);
                dbManager.AddParameters(5, "@GenderSeq", Info.Genders.Sequence);
                dbManager.AddParameters(6, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(7, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(8, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE MAS_NAME_PREFIXES"
                    + " SET PREFIX_NAME = @Name"
                        + ", PREFIX_NAME_ENG = @NameEng"
                        + ", PREFIX_SHORT = @NameShort"
                        + ", PREFIX_SHORT_ENG = @NameEngShort"
                        + ", GENDER_SEQ = @GenderSeq"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE NAME_PREFIX_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw new Exception("{class MasNamePrefixDAL}:" + ex.Message); 
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE MAS_NAME_PREFIXES"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE NAME_PREFIX_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw new Exception("{class MasNamePrefixDAL}:" + ex.Message); 
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
